﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;
using System.Web.Security;

namespace VK.Casemeet.Merchants
{
    internal static class ShopDAL
    {
        internal static ShopEntity GetShopByReader(IDataReader dr)
        {
            ShopEntity shop = new ShopEntity();

            shop.ShopID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            shop.Type = dr.IsDBNull(1) ? 0 : dr.GetInt32(1);
            shop.Name = dr.IsDBNull(2) ? null : dr.GetString(2);
            shop.ProvinceID = dr.IsDBNull(3) ? 0 : dr.GetInt32(3);
            shop.CityID = dr.IsDBNull(4) ? 0 : dr.GetInt32(4);
            shop.AreaID = dr.IsDBNull(5) ? 0 : dr.GetInt32(5);
            shop.Level = dr.IsDBNull(6) ? 0 : dr.GetInt32(6);
            shop.Address = dr.IsDBNull(7) ? null : dr.GetString(7);
            shop.Telephone = dr.IsDBNull(8) ? null : dr.GetString(8);
            shop.Capital = dr.IsDBNull(9) ? 0 : dr.GetInt32(9);
            shop.Map = dr.IsDBNull(10) ? null : dr.GetString(10);
            shop.IsCertification = dr.IsDBNull(11) ? false : dr.GetBoolean(11);
            shop.State = dr.IsDBNull(12) ? 0 : dr.GetInt32(12);
            shop.Hits = dr.IsDBNull(13) ? 0 : dr.GetInt32(13);
            shop.Notice = dr.IsDBNull(14) ? null : dr.GetString(14);
            shop.LicenceUrl = dr.IsDBNull(15) ? null : dr.GetString(15);
            shop.ProvinceName = dr.IsDBNull(16) ? null : dr.GetString(16);
            shop.CityName = dr.IsDBNull(17) ? null : dr.GetString(17);
            shop.AreaName = dr.IsDBNull(18) ? null : dr.GetString(18);
            shop.LogoUrl = dr.IsDBNull(19) ? "Null.jpg" : dr.GetString(19);
            shop.Contact = dr.IsDBNull(20) ? string.Empty : dr.GetString(20);
            shop.Email = dr.IsDBNull(21) ? string.Empty : dr.GetString(21);
            shop.Company = dr.IsDBNull(22) ? string.Empty : dr.GetString(22);
            shop.QQ = dr.IsDBNull(23) ? string.Empty : dr.GetString(23);
            shop.SiteUrl = dr.IsDBNull(24) ? string.Empty : dr.GetString(24);

            return shop;
        }

        internal static int GetShopCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectShop = @"SELECT COUNT([Shop].[ShopID]) FROM [Shop] 
                LEFT OUTER JOIN Area AS ProvinceArea ON Shop.ProvinceID = ProvinceArea.AreaID 
                LEFT OUTER JOIN Area AS CityArea ON Shop.CityID = CityArea.AreaID
                LEFT OUTER JOIN Area AS AreaArea ON Shop.AreaID = AreaArea.AreaID 
                INNER JOIN Shop_Admin ON Shop.ShopID = Shop_Admin.ShopID ";
            var sercon = conditionArray.Where(p => p.FieldName == "ServiceID");
            if (sercon.Count() > 0 && sercon.First().ConditionValue.ToString() != "0")
            {
                SelectShop += " INNER JOIN Shop_Service_Mapping ON Shop.ShopID = Shop_Service_Mapping.ShopID ";
            }

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectShop, ref parameterlst);
            DbCommand SelectShopCommand = db.GetSqlStringCommand(SelectShop);
            DbHelper.PackDbCommandByDbParameters(SelectShopCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectShopCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<ShopEntity> GetShopList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<ShopEntity> shoplst = new List<ShopEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectShop = @"
                    SELECT [ShopID],[Type],[Name],[ProvinceID],[CityID],[AreaID],[Level],[Address],[Telephone],
                        [Capital],[Map],[IsCertification],[State],[Hits],[Notice],[LicenceUrl],[ProvinceName],
                        [CityName],[AreaName],LogoUrl,Contact,Email,Company,[QQ],[SiteUrl],[Account],[AdminID]
                    FROM (
                        SELECT [Shop].[ShopID],[Shop].[Type],[Shop].[Name],[Shop].[ProvinceID],[Shop].[CityID],
                            [Shop].[AreaID],[Shop].[Level],[Shop].[Address],[Shop].[Telephone],[Shop].[Capital],[Shop].[Map],
                            [Shop].[IsCertification],[Shop].[State],[Shop].[Hits],[Shop].[Notice],[Shop].[LicenceUrl],
                            ProvinceArea.Name AS ProvinceName, CityArea.Name AS CityName, AreaArea.Name AS AreaName,
                            [Shop].LogoUrl,[Shop].Contact,[Shop].Email,[Shop].Company,[Shop].[QQ],[Shop].[SiteUrl],Shop_Admin.Account,
                            Shop_Admin.AdminID,ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectShop += " " + sortField + " " + sortMode + ",";
            SelectShop += @" Shop.[ShopID] Desc
                    ) AS rowNum 
                        FROM Shop 
                            LEFT OUTER JOIN Area AS ProvinceArea ON Shop.ProvinceID = ProvinceArea.AreaID 
                            LEFT OUTER JOIN Area AS CityArea ON Shop.CityID = CityArea.AreaID
                            LEFT OUTER JOIN Area AS AreaArea ON Shop.AreaID = AreaArea.AreaID 
                            INNER JOIN Shop_Admin ON Shop.ShopID = Shop_Admin.ShopID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectShop, ref parameterlst);
            SelectShop += @"
                    ) AS [shop] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectShopCommand = db.GetSqlStringCommand(SelectShop);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectShopCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectShopCommand))
            {
                while (dr.Read())
                {
                    ShopEntity shop = GetShopByReader(dr);
                    shop.AdminID = dr["AdminID"] == null ? 0 : (int)dr["AdminID"];
                    shop.Account = dr["Account"] == null ? null : dr["Account"].ToString();
                    shoplst.Add(shop);
                }
            }

            return shoplst;
        }

        internal static ShopEntity GetShop(int shopID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            ShopEntity shop = null;

            string SelectShop = @"
                    SELECT Shop.ShopID, Shop.Type, Shop.Name, Shop.ProvinceID, Shop.CityID, Shop.AreaID, Shop.[Level], 
                      Shop.Address, Shop.Telephone, Shop.Capital, Shop.Map, Shop.IsCertification, Shop.State, Shop.Hits, 
                      Shop.[Notice],Shop.[LicenceUrl], ProvinceArea.Name AS ProvinceName, CityArea.Name AS CityName, 
                      AreaArea.Name AS AreaName, Shop.LogoUrl,[Shop].Contact,[Shop].Email,[Shop].Company,
                      [Shop].[QQ],[Shop].[SiteUrl],Shop_Admin.AdminID,Shop_Admin.Account, Shop_Admin.Password
                    FROM Shop 
                        INNER JOIN Shop_Admin ON Shop.ShopID = Shop_Admin.ShopID
                        LEFT OUTER JOIN Area AS ProvinceArea ON Shop.ProvinceID = ProvinceArea.AreaID 
                        LEFT OUTER JOIN Area AS CityArea ON Shop.CityID = CityArea.AreaID
                        LEFT OUTER JOIN Area AS AreaArea ON Shop.AreaID = AreaArea.AreaID
                    WHERE Shop.ShopID=@ShopID
                ";

            DbCommand SelectShopCommand = db.GetSqlStringCommand(SelectShop);
            db.AddInParameter(SelectShopCommand, "@ShopID", DbType.Int32, shopID);
            using (IDataReader dr = db.ExecuteReader(SelectShopCommand))
            {
                if (dr.Read())
                {
                    shop = GetShopByReader(dr);
                    shop.AdminID = dr["AdminID"] == null ? 0 : (int)dr["AdminID"];
                    shop.Account = dr["Account"] == null ? null : dr["Account"].ToString();
                    shop.Password = dr["Password"] == null ? null : dr["Password"].ToString();
                }
            }

            return shop;
        }

        internal static int InsertShop(ShopEntity shop)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder InsertShop = new StringBuilder();
            InsertShop.Append(@"Declare @ShopID int; INSERT INTO [Shop] 
				([Type],[Name],[ProvinceID],[CityID],[AreaID],[Level],[Address],[Telephone],[Capital],[Map],
                [IsCertification],[State],[Hits],[Notice],[LicenceUrl],[Contact],[Email],[Company],[LogoUrl],[QQ],[SiteUrl]) VALUES (@Type,@Name,@ProvinceID,@CityID,
                @AreaID,@Level,@Address,@Telephone,@Capital,@Map,@IsCertification,@State,@Hits,@Notice,@LicenceUrl,
                @Contact,@Email,@Company,@LogoUrl,@QQ,@SiteUrl); SELECT @ShopID=@@IDENTITY;
                INSERT INTO [Shop_Admin] ([ShopID],[Account],[Password],[Logins],[LastDateTime],[Created])
                VALUES (@ShopID,@Account,@Password,1,@LastDateTime,@Created);");
            DbCommand InsertShopCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_InsertShop", InsertShop.ToString()));
            db.AddInParameter(InsertShopCommand, "@Type", DbType.Int32, shop.Type);
            db.AddInParameter(InsertShopCommand, "@Name", DbType.String, shop.Name);
            db.AddInParameter(InsertShopCommand, "@ProvinceID", DbType.Int32, shop.ProvinceID);
            db.AddInParameter(InsertShopCommand, "@CityID", DbType.Int32, shop.CityID);
            db.AddInParameter(InsertShopCommand, "@AreaID", DbType.Int32, shop.AreaID);
            db.AddInParameter(InsertShopCommand, "@Level", DbType.Int32, shop.Level);
            db.AddInParameter(InsertShopCommand, "@Address", DbType.String, shop.Address);
            db.AddInParameter(InsertShopCommand, "@Telephone", DbType.String, shop.Telephone);
            db.AddInParameter(InsertShopCommand, "@Capital", DbType.Int32, shop.Capital);
            db.AddInParameter(InsertShopCommand, "@Map", DbType.String, shop.Map);
            db.AddInParameter(InsertShopCommand, "@IsCertification", DbType.Boolean, shop.IsCertification);
            db.AddInParameter(InsertShopCommand, "@State", DbType.Int32, shop.State);
            db.AddInParameter(InsertShopCommand, "@Hits", DbType.Int32, shop.Hits);
            db.AddInParameter(InsertShopCommand, "@Notice", DbType.String, shop.Notice);
            db.AddInParameter(InsertShopCommand, "@LicenceUrl", DbType.String, shop.LicenceUrl);
            db.AddInParameter(InsertShopCommand, "@Account", DbType.String, shop.Account);
            db.AddInParameter(InsertShopCommand, "@Password", DbType.String, shop.Password);
            db.AddInParameter(InsertShopCommand, "@LastDateTime", DbType.DateTime, DateTime.Now);
            db.AddInParameter(InsertShopCommand, "@Created", DbType.DateTime, DateTime.Now);
            db.AddInParameter(InsertShopCommand, "@Contact", DbType.String, shop.Contact);
            db.AddInParameter(InsertShopCommand, "@Email", DbType.String, shop.Email);
            db.AddInParameter(InsertShopCommand, "@Company", DbType.String, shop.Company);
            db.AddInParameter(InsertShopCommand, "@LogoUrl", DbType.String, shop.LogoUrl);
            db.AddInParameter(InsertShopCommand, "@QQ", DbType.String, shop.QQ);
            db.AddInParameter(InsertShopCommand, "@SiteUrl", DbType.String, shop.SiteUrl);

            result = db.ExecuteNonQuery(InsertShopCommand);

            return result;
        }

        internal static int UpdateShop(ShopEntity shop)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder UpdateShop = new StringBuilder();
            UpdateShop.Append(@"UPDATE [Shop] 
				SET [Type]=@Type,[Name]=@Name,[ProvinceID]=@ProvinceID,[CityID]=@CityID,[AreaID]=@AreaID,
                    [Level]=@Level,[Address]=@Address,[Telephone]=@Telephone,[Capital]=@Capital,[Map]=@Map,
                    [IsCertification]=@IsCertification,[State]=@State,[Hits]=@Hits,Notice=@Notice,LicenceUrl=@LicenceUrl,
                    [Contact]=@Contact,[Email]=@Email,[Company]=@Company,[LogoUrl]=@LogoUrl,[QQ]=@QQ,[SiteUrl]=@SiteUrl
				WHERE [ShopID] = @ShopID;
                UPDATE [Shop_Admin] SET [Account]=@Account,[Password]=@Password WHERE [AdminID] = @AdminID;");
            DbCommand UpdateShopCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_InsertShop", UpdateShop.ToString()));
            db.AddInParameter(UpdateShopCommand, "@ShopID", DbType.Int32, shop.ShopID);
            db.AddInParameter(UpdateShopCommand, "@Type", DbType.Int32, shop.Type);
            db.AddInParameter(UpdateShopCommand, "@Name", DbType.String, shop.Name);
            db.AddInParameter(UpdateShopCommand, "@ProvinceID", DbType.Int32, shop.ProvinceID);
            db.AddInParameter(UpdateShopCommand, "@CityID", DbType.Int32, shop.CityID);
            db.AddInParameter(UpdateShopCommand, "@AreaID", DbType.Int32, shop.AreaID);
            db.AddInParameter(UpdateShopCommand, "@Level", DbType.Int32, shop.Level);
            db.AddInParameter(UpdateShopCommand, "@Address", DbType.String, shop.Address);
            db.AddInParameter(UpdateShopCommand, "@Telephone", DbType.String, shop.Telephone);
            db.AddInParameter(UpdateShopCommand, "@Capital", DbType.Int32, shop.Capital);
            db.AddInParameter(UpdateShopCommand, "@Map", DbType.String, shop.Map);
            db.AddInParameter(UpdateShopCommand, "@IsCertification", DbType.Boolean, shop.IsCertification);
            db.AddInParameter(UpdateShopCommand, "@State", DbType.Int32, shop.State);
            db.AddInParameter(UpdateShopCommand, "@Hits", DbType.Int32, shop.Hits);
            db.AddInParameter(UpdateShopCommand, "@Notice", DbType.String, shop.Notice);
            db.AddInParameter(UpdateShopCommand, "@LicenceUrl", DbType.String, shop.LicenceUrl);
            db.AddInParameter(UpdateShopCommand, "@Account", DbType.String, shop.Account);
            db.AddInParameter(UpdateShopCommand, "@Password", DbType.String, shop.Password);
            db.AddInParameter(UpdateShopCommand, "@AdminID", DbType.Int32, shop.AdminID);
            db.AddInParameter(UpdateShopCommand, "@Contact", DbType.String, shop.Contact);
            db.AddInParameter(UpdateShopCommand, "@Email", DbType.String, shop.Email);
            db.AddInParameter(UpdateShopCommand, "@Company", DbType.String, shop.Company);
            db.AddInParameter(UpdateShopCommand, "@LogoUrl", DbType.String, shop.LogoUrl);
            db.AddInParameter(UpdateShopCommand, "@QQ", DbType.String, shop.QQ);
            db.AddInParameter(UpdateShopCommand, "@SiteUrl", DbType.String, shop.SiteUrl);

            result = db.ExecuteNonQuery(UpdateShopCommand);

            return result;
        }

        internal static int DeleteShop(int shopID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteShop = @"
                DELETE FROM [TravelLog] WHERE ObjectType=1 AND ObjectID=@ShopID;
                DELETE FROM [Album] WHERE ObjectType=1 AND ObjectID=@ShopID;
                DELETE FROM [Photo] FROM [Photo] INNER JOIN Album ON Photo.ObjectID = Album.AlbumID AND Photo.ObjectType=3 WHERE Album.ObjectType=1 AND Album.ObjectID=@ShopID;
                DELETE FROM [Line] WHERE ShopID=@ShopID;
                DELETE FROM [LineSchedule] FROM [LineSchedule] INNER JOIN Line ON LineSchedule.LineID = Line.LineID WHERE Line.ShopID=@ShopID;
                DELETE FROM [LineFeature] FROM [LineFeature] INNER JOIN Line ON LineFeature.LineID = Line.LineID WHERE Line.ShopID=@ShopID;
                DELETE FROM [Photo] FROM [Photo] INNER JOIN Line ON Photo.ObjectID = Line.LineID AND Photo.ObjectType=1 WHERE Line.ShopID=@ShopID;
                DELETE FROM [Groups] WHERE ShopID=@ShopID;
                DELETE FROM [Shop_Admin] WHERE ShopID=@ShopID;
                DELETE FROM [Shop] WHERE ShopID=@ShopID;";

            DbCommand DeleteShopCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_DeleteShop", DeleteShop));
            db.AddInParameter(DeleteShopCommand, "@ShopID", DbType.Int32, shopID);
            result = db.ExecuteNonQuery(DeleteShopCommand);

            return result;
        }

        internal static int SetShopEnable(int shopID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetShop = @"UPDATE [Shop]
                    SET [IsEnable] = ([IsEnable]+1)%2
                    WHERE [ShopID] = @ShopID";

            DbCommand SetShopCommand = db.GetSqlStringCommand(SetShop);
            db.AddInParameter(SetShopCommand, "@ShopID", DbType.Int32, shopID);
            result = db.ExecuteNonQuery(SetShopCommand);

            return result;
        }

        internal static int HitShop(int shopID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetShop = @"UPDATE [Shop]
                    SET [Hits] = [Hits]+1
                    WHERE [ShopID] = @ShopID";

            DbCommand SetShopCommand = db.GetSqlStringCommand(SetShop);
            db.AddInParameter(SetShopCommand, "@ShopID", DbType.Int32, shopID);
            result = db.ExecuteNonQuery(SetShopCommand);

            return result;
        }
    }
}